﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;

/// <summary>
/// studentDao 的摘要说明
/// </summary>
public class StudentDao
{
    //获取数据库连接串
    private static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MySQLConnectionString"].ConnectionString;

    /// <summary>
    /// 
    ///获取Info表中所有的记录
    /// </summary>
    /// <returns></returns>
    public static List<StudentInfo> FindAll(int start, int num)
    {
        List<StudentInfo> data = new List<StudentInfo>();
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            string sql = "select tb_student.*,tb_class.class_name "
                +" from tb_student,tb_class "
                +" where tb_student.class_code=tb_class.class_code "
                +" limit " + start + "," + num;
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                StudentInfo studentInfo = new StudentInfo();
                studentInfo.Id = Convert.ToInt64(rdr["id"]);
                studentInfo.StudentNo = Convert.ToString(rdr["student_no"]);
                studentInfo.StudentName = Convert.ToString(rdr["student_name"]);
                studentInfo.Gender = Convert.ToString(rdr["gender"]);
                studentInfo.ClassName = Convert.ToString(rdr["class_name"]);

                data.Add(studentInfo);
            }
            rdr.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        return data;
    }


    public static StudentInfo FindOne(long id)
    {
        StudentInfo data = new StudentInfo();

        MySqlConnection conn = new MySqlConnection(connStr);

        try
        {
            conn.Open();
            //sql语句采用占位符，更加安全，可在一定程度上防止sql注入
            //string sql = "select * from tb_course where id = @id ";
            string sql = "select tb_student.*,tb_class.class_name "
                + " from tb_student,tb_class "
                + " where tb_student.class_code=tb_class.class_code and tb_student.id = @tb_student.id ";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@tb_student.id", id);

            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                data.Id = Convert.ToInt64(rdr["id"]);
                data.StudentNo = Convert.ToString(rdr["student_no"]);
                data.StudentName = Convert.ToString(rdr["student_name"]);
                data.StudentNo = Convert.ToString(rdr["gender"]);
                data.StudentName = Convert.ToString(rdr["class_name"]);
            }

            rdr.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Find方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Find方法异常信息=======");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        return data;
    }

    //通过关键字查找
    public static List<StudentInfo> Find(string keyWord)
    {
        List<StudentInfo> data = new List<StudentInfo>();
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            //string sql = "select * from tb_course where course_code=@course_code or course_name=@course_name";
            string sql = "select tb_student.*,tb_class.class_name "
               + " from tb_student,tb_class "
               + " where tb_student.class_code=tb_class.class_code and tb_student.student_no = @student_no or tb_student.student_name = @student_name ";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);

            cmd.Parameters.AddWithValue("@student_no", keyWord);
            cmd.Parameters.AddWithValue("@student_name", keyWord);
            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                StudentInfo studentInfo = new StudentInfo();
                studentInfo.Id = Convert.ToInt64(rdr["id"]);
                studentInfo.StudentNo = Convert.ToString(rdr["student_no"]);
                studentInfo.StudentName = Convert.ToString(rdr["student_name"]);
                studentInfo.Gender = Convert.ToString(rdr["gender"]);
                studentInfo.ClassName = Convert.ToString(rdr["class_name"]);

                data.Add(studentInfo);
            }
            rdr.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        return data;
    }

   
}